using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using StillVoiceCommon;
using TN5Y.Server;
using TN5Y;
using Microsoft.ApplicationBlocks.Data;
using System.Data.SqlClient;
using System.Text;
using System.Collections.Generic;
using System.IO;
/// <summary>
/// Summary description for StaffMember
/// </summary>

namespace TeamTime.Classes
{

    public class StaffMember : StillVoiceCommon.DataTableRow
    {
        #region properties

        private string usernameModifiedBy;
        public new string UserNameModifiedBy
        {
            get { return usernameModifiedBy; }
            set { usernameModifiedBy = value; }
        }
        public string Designation;
        public string FullNameAndID { get; set; }
        public string FullName { get; set; }
        #endregion

        public StaffMember()
        {
            this.initClass(TN5Y.Utilities.DBConnectionString, "StaffMember");
        }
        public new void load(string id)
        {
            base.load(id);
            System.Data.SqlClient.SqlDataReader dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text,
                "select * from ViewStaffMembers where ID = " + @id,
                new SqlParameter("id", id));
            if (dr.Read())
            {
                usernameModifiedBy = dr["UserNameModifiedBy"].ToString();
                FullNameAndID = dr["FullNameAndID"].ToString();
                FullName = dr["FullName"].ToString();
                Designation = dr["Designation"].ToString();
            }
            dr.Close();
            dr = null;
        }
        public new void updateWithUserIDDate()
        {

            base.updateWithUserIDDate();

            //Attempt to create and/or connect the Staff Member to a system user.
            User user = new User();
            user.loadByPersonID(Convert.ToInt32(this.Fields["PersonID"])); //Attempt to find the user for this StaffMember/Person
            if (user.KeyValue == "0" || user.KeyValue == "") //Not found
            {
                user.loadByName(this.FullName);
                if (Convert.ToInt32(user.KeyValue) > 0) //Found
                {
                    if (user.Fields["PersonID"] == DBNull.Value)  //A disconnected User record
                    {
                        user.Fields["PersonID"] = this.Fields["PersonID"]; //Connect him
                        user.update();
                    }
                }
                else //not found
                {
                    //Create a new user and connect him
                    Person person = new Person(); person.load(Convert.ToInt32(this.Fields["PersonID"]));
                    user.setForAdd();

                    user.Fields["LastName"] = person.Fields["Surname"];
                    user.Fields["FirstName"] = person.Fields["FirstName"];
                    user.Fields["LoginCode"] = person.Fields["FirstName"].ToString() + person.Fields["FirstName"].ToString().Substring(0, 1);
                    user.Fields["Password"] = "password";
                    UserGroup usergroup = new UserGroup();
                    usergroup.loadByName(this.Designation); //Handles the Consultant(s) case
                    if (usergroup.KeyValue != "0") //Found
                        user.Fields["DefaultUserGroupID"] = usergroup.KeyValue;
                   

                    user.Fields["Email"] = person.Fields["Email"];
                    user.Fields["InactiveDate"] = DBNull.Value;
                    user.Fields["ReadOnly"] = 0;
                    user.Fields["CellphoneNumber"] = person.Fields["TelCell"];
                    user.Fields["PersonID"] = this.Fields["PersonID"];
                    try
                    {
                        user.updateWithUserIDDate(); //which adds the user to the default group if necessary
                        
                    }
                    catch { }
                }
            }
            else //Found
            {
                UserGroup usergroup = new UserGroup();
                usergroup.loadByName(this.Designation);
                if (usergroup.KeyValue != "0") //Found
                {
                    if (user.Fields["DefaultUserGroupID"].ToString() != usergroup.KeyValue)
                    {
                        user.Fields["DefaultUserGroupID"] = usergroup.KeyValue;
                        user.updateWithUserIDDate();//which adds the user to the default group idf necessary
                    }
                }
            }
            if (this.Designation!=null && this.Designation.ToLower().Contains("consultant"))
            {
                //Try to create a consultant record for the StaffMember, if it doesn't exist
                Consultant consultant = new Consultant();
                consultant.loadByStaffMemberID(Convert.ToInt32(this.KeyValue));
                if (consultant.KeyValue == "0") //Not found
                {
                    consultant.setForAdd();
                    consultant.Fields["StaffMemberID"] = this.KeyValue;
                    consultant.update();
                }
            }
        }

        internal void loadByUserID(int userID)
        {
            string sql = "select sm.ID from StaffMember sm inner join Person p on sm.PersonID = p.ID " +
                        "inner join Security.[User] u on p.ID = u.PersonID where u.ID = @UserID ";
            Int32 ID = Convert.ToInt32(SqlHelper.ExecuteScalar(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql,
                new SqlParameter("UserID", userID)));
            if (ID > 0)
            {
                this.load(ID);
            }
        }

        internal void setOfficeDropDown(DropDownList ddlOfficeID, int userID)
        {
            //assumes the OfficeID drop down already populated with records
            User user = new User(userID);
            user.load(userID);
            if (user.CanDo("ViewAllOffices"))
            {
                ddlOfficeID.Enabled = true;
            }
            else
            {
                ddlOfficeID.Enabled = false;
                //Default the ddlOfficeID
                StaffMember staffMember = new StaffMember();
                staffMember.loadByUserID(userID);
                if (staffMember.KeyValue == "0") // not loaded
                {
                    throw new Exception("StaffMember.setOfficeDropDown: No staff Member record found for UserID:" + userID.ToString());
                }
                ddlOfficeID.SelectedValue = staffMember.Fields["OfficeID"].ToString();
            }
        }

        //internal void setProvinceDropDown(DropDownList ddlOfficeID, int userID)
        //{
        //    //assumes the OfficeID drop down already populated with records
        //    User user = new User(userID);
        //    user.load(userID);
        //    if (user.CanDo("ViewAllOffices"))
        //    {
        //        ddlOfficeID.Enabled = true;
        //    }
        //    else
        //    {
        //        ddlOfficeID.Enabled = false;
        //        //Default the ddlOfficeID
        //        StaffMember staffMember = new StaffMember();
        //        staffMember.loadByUserID(userID);
        //        if (staffMember.KeyValue == "0") // not loaded
        //        {
        //            throw new Exception("StaffMember.setOfficeDropDown: No staff Member record found for UserID:" + userID.ToString());
        //        }
        //        ddlOfficeID.SelectedValue = staffMember.Fields["OfficeID"].ToString();
        //    }
        //}
    }

    public class StaffMembers
    {
        public DataSet searchStaffMembers(string searchFor, string sortExpression)
        {
            StringBuilder sql = new StringBuilder("SELECT * FROM ViewStaffMembers where 1=1 ");
            if (searchFor != "")
            {
                sql.Append(" and (FullName like '%'+@searchFor+'%' or");
                sql.Append(" PositionHeld like '%'+@searchFor+'%' ");

                sql.Append(") ");
            }

            if (sortExpression != "")
            {
                sql.Append(" order by " + sortExpression);
            }
            return SqlHelper.ExecuteDataset(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql.ToString(),
                                                new SqlParameter("searchFor", searchFor));
        }

        public bool duplicateStaffMemberExists(int StaffMember_PersonID, string ID)
        {
            bool ret = false;
            string sql = "select ID from ViewStaffMembers where PersonID = '" + StaffMember_PersonID + "' ";
            if (ID != "") { sql += " and ID <> '" + ID + "'"; }//same name different id
            SqlDataReader dr;
            dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql);
            ret = dr.Read();
            dr.Close();
            return ret;
        }

        public int getDuplicateStaffMemberID(int StaffMember_PersonID, string ID)
        {
            int ret = 0;
            string sql = "select ID from StaffMember where PersonID = '" + StaffMember_PersonID + "' ";
            if (ID != "") { sql += " and ID <> '" + ID + "'"; }//same name different id
            SqlDataReader dr;
            dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql);
            if (dr.Read())
            {
                ret = Convert.ToInt32(dr[0]);
            }
            dr.Close();
            return ret;
        }

        internal string GetStaffMemberEmail(Int32 StaffMemberID)
        {
            string ret = "";
            string sql = "select EmailAddress from ViewStaffMembers where ID = @StaffMemberID";

            SqlDataReader dr;
            dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql,
                new SqlParameter("StaffMemberID", StaffMemberID));
            if (dr.Read())
            {
                ret = dr[0].ToString();
            }
            dr.Close();
            return ret;
        }

        public int GetConsultantIDFromName(string FullNameAndID)
        {
            int ret = -1;
            string sql = "select ConsultantID from ViewStaffMembers where FullNameAndID = @FullNameAndID";

            SqlDataReader dr;
            dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql,
                new SqlParameter("FullNameAndID", FullNameAndID));
            if (dr.Read())
            {
                ret = Convert.ToInt32(dr[0].ToString());
            }
            dr.Close();
            return ret;
        }


        internal int getStaffMemberIDFromUserID(int userID)
        {
            string sql = "select isnull(sm.ID,0) from StaffMember sm  " +
                   " inner join Security.[User] u on sm.PersonID = u.PersonID where u.ID = @userID";
            return Convert.ToInt32(SqlHelper.ExecuteScalar(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql,
                new SqlParameter("userID", userID)));
        }

        internal int getUserIDFromConsultantID(int consultantID)
        {
            string sql = "select isnull(u.ID,0) from Consultant c inner join StaffMember sm on c.StaffMemberID = sm.ID " +
                    " inner join Security.[User] u on sm.PersonID = u.PersonID where c.ID = @ConsultantID";
            return Convert.ToInt32(SqlHelper.ExecuteScalar(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql,
                new SqlParameter("ConsultantID", consultantID)));
        }
        internal DataSet getStaffMembers(bool anyOption)
        {
            string sql = "select ID, FullName from ViewStaffMembers ";

            if (anyOption) sql = "select 0 as ID,'<Any>' as FullName UNION " + sql;
            return SqlHelper.ExecuteDataset(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql);
        }
        internal int getStaffMamberIDFromName(string FullNameAndID)
        {
            int ret = -1;
            string sql = "select ID from ViewStaffMembers where FullNameAndID = @FullNameAndID";

            SqlDataReader dr;
            dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql,
                new SqlParameter("FullNameAndID", FullNameAndID));
            if (dr.Read())
            {
                ret = Convert.ToInt32(dr[0].ToString());
            }
            dr.Close();
            return ret;
        }
    }


    public class Consultant : StillVoiceCommon.DataTableRow
    {
        #region properties

        public string Designation;
        public string FullNameAndID { get; set; }
        public string FullName { get; set; }
        public string Email { get; set; }
        #endregion

        public Consultant()
        {
            this.initClass(TN5Y.Utilities.DBConnectionString, "Consultant");
        }
        public new void load(Int32 id)
        {
            base.load(id);
            System.Data.SqlClient.SqlDataReader dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text,
                "select * from ViewConsultants where ID = " + @id,
                new SqlParameter("id", id));
            if (dr.Read())
            {
                FullNameAndID = dr["FullNameAndID"].ToString();
                FullName = dr["FullName"].ToString();
                Designation = dr["Designation"].ToString();
                Email = dr["Email"].ToString();
            }
            dr.Close();
            dr = null;
        }

        internal void loadByStaffMemberID(int staffMemberID)
        {
            Int32 ID = 0;
            string sql = "select ID from Consultant where StaffMemberID = @StaffMemberID";
            try
            {
                ID = Convert.ToInt32(SqlHelper.ExecuteScalar(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql,
                    new SqlParameter("StaffMemberID", staffMemberID)));
                this.load(ID);
            }
            catch (Exception ex ){ 
                if (!ex.Message.Contains("no rec"))
                { throw ex;}
            }


        }
    }
}